package com.yc.book.util;
import java.sql.Blob;
/*
 * 
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {
	static {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	//获取连接的方法
	public Connection getConnection() {
		Connection con = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/books?useUnicode=true&characterEncoding=UTF-8&dateFormat=yyyy-MM-dd",
					"root","123456"
					);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}
	
	//释放资源的方法
	private void close(Connection con,PreparedStatement pstmt,ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	private void close(Connection con,PreparedStatement pstmt) {
		this.close(con,pstmt,null);
	}

	//给预编译执行语句块中的占位符赋值
	protected void setParams(PreparedStatement pstmt,Object ...params ) {
		if(params == null || params.length <= 0) {
			return;
		}
		
		for(int i = 0 ,len = params.length;i < len ;++i) {
			try {
				pstmt.setObject(i+1, params[i]);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//从结果集中获取列的名称
	public String[] getColumnNames(ResultSet rs) {
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();//获取结果集中的数量
			String[] names = new String[count];
			for(int i = 0 ; i < count ; ++ i) {
				names[i] = rsmd.getColumnName(i+1).toLowerCase();
			}
			return names;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	//更新操作
	public int update(String sql,Object ... params) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			this.setParams(pstmt, params);
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close(con, pstmt);
		}
		return -1;
	}
	
	//多行查询  结果集中有多行数据->List  一行中有多个字段->Map
	public List<Map<String,String>> list(String sql,Object ... params){
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = this.getConnection();//获取连接
			pstmt = con.prepareStatement(sql);//预编译执行语句
			this.setParams(pstmt, params);//给预编译执行语句中的占位符赋值
			rs = pstmt.executeQuery();//执行获取结果集
			//获取结果集中的列名
			String[] columnNames = this.getColumnNames(rs);
			if(columnNames == null) {
				return Collections.emptyList();
			}
			Map<String,String> map = null;
			while(rs.next()) {//遍历每一行数据
				//以列名为键，对应列的值为值
				map = new HashMap<String,String>();
				//循环获取每一个列的值
				for(String cname : columnNames) {
					map.put(cname, rs.getString(cname));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			this.close(con, pstmt, rs);
		}
		return list;
	}
	
	public List<Map<String,String>> list(String sql,List<Object> params){
		if(params == null || params.isEmpty()) {
			return this.list(sql);
		}
		return this.list(sql, params.toArray());
	}
	
	public List<Map<String,Object>> listObject(String sql, Object ...params){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = this.getConnection();
			pstmt = con.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();
			
			String[] columnNames = this.getColumnNames(rs);
			if(columnNames == null) {
				return Collections.emptyList();
			}
			
			Map<String,Object> map = null;
			Object obj = null;
			String colunmType = null;
			Blob blob = null;
			byte[] bt = null;
			while(rs.next()) {
				map = new HashMap<String,Object>();
				
				for(String cname:columnNames) {
					obj = rs.getObject(cname);
					if(obj == null) {
						map.put(cname, obj);
						continue;
					}
					
					//获取这一列的数据
					colunmType = obj.getClass().getSimpleName();//获取这个列值的类型名称，不带包路径
					if("BLOB".equals(colunmType)) {
						blob = rs.getBlob(cname);
						bt = blob.getBytes(1, (int)blob.length());
						map.put(cname, bt);
						continue;
					}
					map.put(cname, obj);
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close(con, pstmt, rs);
		}
		return list;
	}
	
	
	public List<Map<String,Object>> listObject(String sql,List<Object> params){
		if(params == null || params.isEmpty()) {
			return this.listObject(sql);
		}
		return this.listObject(sql, params.toArray());
	}
	
	//单行查询
	public Map<String,String> find(String sql , Object ... params){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String,String> map = new HashMap<String,String>();
		
		try {
			con=this.getConnection();
			pstmt=con.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs=pstmt.executeQuery();
			
			String[] columnNames = this.getColumnNames(rs);
			if(columnNames == null) {
				return Collections.emptyMap();
			}
			if(rs.next()) {
				for(String cname : columnNames) {
					map.put(cname, rs.getString(cname));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close(con, pstmt, rs);
		}
		
		return map;
	}
	
	public Map<String,String> find(String sql,List<Object> params){
		if(params == null || params.isEmpty()) {
			return this.find(sql);
		}
		return this.find(sql, params.toArray());
	}
	
	public Map<String,Object> findObject(String sql,Object ...params){
		Map<String,Object> map = new HashMap<String,Object>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = this.getConnection();
			pstmt = con.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();
			
			String[] columnNames =this.getColumnNames(rs);
			if(columnNames == null) {
				return map;
			}
			Object obj = null;
			String ColumnType = null;
			Blob blob = null;
			byte[] bt = null;
			if(rs.next()) {
				for(String cname:columnNames) {
					obj = rs.getObject(cname);
					if(obj == null) {
						map.put(cname, obj);
						continue;
					}
					
					ColumnType = obj.getClass().getSimpleName();
					if("BLOB".equals(ColumnType)) {
						blob = rs.getBlob(cname);
						bt = blob.getBytes(1, (int)blob.length());
						map.put(cname, bt);
						continue;
					}
					map.put(cname, obj);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			this.close(con, pstmt, rs);
		}
		return map;
	}
	
	public Map<String,Object> findObject(String sql , List<Object> params){
		if(params == null || params.isEmpty()) {
			return this.findObject(sql);
		}
		return this.findObject(sql, params.toArray());
	}
	
	//统计 count(*)
	public int total(String sql , Object ... params) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = this.getConnection();
			pstmt = con.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close(con, pstmt, rs);
		}
		
		return 0 ;
	}
	
	public int total(String sql,List<Object> params) {
		if(params == null || params.isEmpty()) {
			return this.total(sql);
		}
		return this.total(sql, params.toArray());
	}
	
	
	
	
}

